//
//  ZQDBHelper.m
//  ZQSmartLife
//
//  Created by huanghuan on 15/6/10.
//  Copyright (c) 2015年 huanghuan. All rights reserved.
//

#import "ZQDBHelper.h"
#import "ZQTableHelper.h"


#define DBVersionKey @"DBVersionKey"

@implementation ZQDBHelper
static ZQDBHelper *p_sInstance;

+(id)sharedInstance
{
    if (p_sInstance){
        return p_sInstance;
    }
    else{
        p_sInstance = [[ZQDBHelper alloc] init];
    }
    return p_sInstance;
}

-(id) init
{
    self = [super init];
    if (self)
    {
      //  [self initDataBaseWithVersion:0];
    }
    return self;
}

//创建数据库
-(BOOL) createDataBase
{
    //paths： ios下Document路径，Document为ios中可读写的文件夹
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentDictionary = [paths objectAtIndex:0];
    //数据库路径
    dbPath = [documentDictionary stringByAppendingPathComponent:@"/ismartdbSDK.sqlite"];
    NSFileManager *fileMgr = [NSFileManager defaultManager];
    if ([fileMgr fileExistsAtPath:dbPath]) //如果已经存在，则返回YES
    {
        return YES;
    }
    else
    {
        return [fileMgr createFileAtPath:dbPath contents:nil attributes:nil];
    }
    
}

//初始化数据库
-(BOOL)initDataBaseWithVersion:(int) version
{
    NSNumber *verNum = [[NSUserDefaults standardUserDefaults] objectForKey:DBVersionKey];
    if([self createDataBase])
    {
        BOOL ret = NO;
        if (verNum == nil) {
            ret = [self createTable];
            [[NSUserDefaults standardUserDefaults] setObject:[NSNumber numberWithInt:1] forKey:DBVersionKey];
        }
        else if (version > verNum.intValue){
            //升级
        }else{
            ret = YES;
            
        }
       [self openDB];
        return ret;
    }
    else
    {
        return NO;
    }
}

//创建表
-(BOOL)createTable
{
    [self openDB];
    NSMutableArray *sqlArray = [[NSMutableArray alloc] initWithCapacity:0];
    [sqlArray addObject:[ZQTableHelper createDeviceTab]];
    return [self execMultipleSql:sqlArray];
}

#pragma -mark 打开数据库
-(void)openDB
{
    
    sqlite3_shutdown();
    int err=sqlite3_config(SQLITE_CONFIG_SERIALIZED);
    if (err == SQLITE_OK) {
        NSLog(@"Can now use sqlite on multiple threads, using the same connection");
    } else {
        NSLog(@"setting sqlite thread safe mode to serialized failed!!! return code: %d", err);
    }
    
    if(sqlite3_open([dbPath UTF8String], &db) != SQLITE_OK){
        NSLog(@"Failed to open database");
        [[NSUserDefaults standardUserDefaults] setObject:@"DB open failed" forKey:@"OpenDb"];
    }
}


#pragma -mark 增 删 改 数据库
-(BOOL)execSql:(NSString *) sql
{
    @synchronized([ZQDBHelper class]){
        char *err;
        if (sqlite3_exec(db, [sql UTF8String], nil, nil, &err) != SQLITE_OK) {
            NSLog(@"exec sql failed!msg:%@",[NSString stringWithUTF8String:err]);
            return NO;
        }
        else{
            return YES;
        }
    }
    
    
    
    
    
}


#pragma -mark 执行事务
-(BOOL)execMultipleSql:(NSMutableArray *)sqlArray
{
    @synchronized([ZQDBHelper class])
    {
        @try {
            char *errorMsg;
            if (sqlite3_exec(db, "BEGIN", NULL, NULL, &errorMsg)==SQLITE_OK)
            {
                NSLog(@"启动事务成功");
                sqlite3_free(errorMsg);
                sqlite3_stmt *statement;
                for (int i = 0; i<sqlArray.count; i++)
                {
                    NSLog(@"sql=====:%@",[sqlArray objectAtIndex:i]);
                    const char *errMsg;
                    if (sqlite3_prepare_v2(db,[[sqlArray objectAtIndex:i] UTF8String], -1, &statement,NULL)==SQLITE_OK){
                        int ret = sqlite3_step(statement);
                            sqlite3_finalize(statement);
                        if (ret != SQLITE_DONE){
                       
                            NSException *e = [NSException
                                              exceptionWithName: @"sql Exception"
                                              reason: [NSString stringWithUTF8String:sqlite3_errmsg(db)]
                                              userInfo: nil];
                            @throw e;
                        }
                    }                }
                if (sqlite3_exec(db, "COMMIT", NULL, NULL, &errorMsg)==SQLITE_OK){
                    NSLog(@"提交事务成功");
                }
                sqlite3_free(errorMsg);
            }
            else {
                sqlite3_free(errorMsg);
            }
        }
        @catch (NSException *exception) {
            NSLog(@"%@",[exception reason]);
            char *errorMsg;
            if (sqlite3_exec(db, "ROLLBACK", NULL, NULL, &errorMsg)==SQLITE_OK)  NSLog(@"回滚事务成功");
            sqlite3_free(errorMsg);
            return NO;
        }
        @finally {
            
        }
    }
    
    
    return YES;
}

#pragma -mark 查询数据库
-(sqlite3_stmt *)query:(NSString *)sql
{
    sqlite3_stmt *statement;
    if(sqlite3_prepare_v2(db, [sql UTF8String], -1, &statement, nil) == SQLITE_OK){
    }
    return statement;
}

-(void)closeDb
{
    if (db)
    {
        
        int rc = sqlite3_close(db);
        NSLog(@"close rc=%d", rc);
        
        if (rc == SQLITE_BUSY)
        {
            
            sqlite3_stmt *stmt;
            while ((stmt = sqlite3_next_stmt(db, 0x00)) != 0)
            {
                NSLog(@"finalizing stmt");
                sqlite3_finalize(stmt);
            }
            
            rc = sqlite3_close(db);
        }
        
        if (rc != SQLITE_OK)
        {
            NSLog(@"close not OK.  rc=%d", rc);
        }
        db = NULL;
    }
    
}



-(void) dealloc
{
    [self closeDb];
}

@end
